import 'package:flutter_rtk/entities/project_entity.dart';
import 'package:flutter_rtk/entities/sync_business_entity.dart';
import 'package:flutter_rtk/extension/string_extension.dart';
import 'package:flutter_rtk/utils/md5_utils.dart';
import 'package:sqflite/sqflite.dart';

/// 功能跑通，可合并优化
class DatabaseService {
  DatabaseService._internal();

  factory DatabaseService() => _instance;
  static final DatabaseService _instance = DatabaseService._internal();

  // 提供对外访问
  static DatabaseService to = DatabaseService();

  late Database _database;

  init() async {
    // 初始化数据库
    _database = await openDatabase('rtk.db', version: 1, onUpgrade: (Database db, int version, int v) async {
      // 初始化基本表
      var syncTableSql = """
      create table if not exists t_business_sync (  
          id TEXT PRIMARY KEY,  
          business TEXT,  
          text TEXT,  
          images TEXT,  
          create_time INTEGER,  
          update_time INTEGER,  
          sync_time INTEGER,  
          is_synchronized INTEGER  
      );
      """;
      await db.execute(syncTableSql);
      // 初始化用户表
      var userTableSql = """
      create table if not exists t_user (  
          id TEXT PRIMARY KEY,  
          username TEXT,
          password TEXT,
          login_info TEXT,
          user_info TEXT
      );
      """;
      await db.execute(userTableSql);
      // 初始化项目
      // project_id,project_code,project_name, dept_code,dept_name, project_type,merge_name, parent_id
      var projectTableSql = """
      create table if not exists t_project (  
          id integer primary key autoincrement,
          project_code TEXT,
          project_id TEXT,
          project_name TEXT,
          dept_code TEXT,
          dept_name TEXT,
          project_type TEXT,
          merge_name TEXT,
          parent_id TEXT,
          is_current INTEGER default 0
      );
      """;
      await db.execute(projectTableSql);
    });
  }

  // --------------------------- business sync --------------------------------
  clearBusinessSync() async {
    if (!_database.isOpen) {
      await init();
    }
    return await _database.delete("t_business_sync");
  }

  insertBusinessSync(String business, String text, String? images) async {
    if (!_database.isOpen) {
      await init();
    }
    var dataId = MD5Utils.toMD5(DateTime.now().millisecondsSinceEpoch.toString());
    var sbe = SyncBusinessEntity(id: dataId, business: business, text: text, images: images ?? "", createTime: DateTime.now().millisecondsSinceEpoch);
    if (await _database.insert("t_business_sync", sbe.toJson(), conflictAlgorithm: ConflictAlgorithm.replace) > 0) {
      return dataId;
    }
    return "";
  }

  updateBusinessSync(String id, String? text, String? images) async {
    if (!_database.isOpen) {
      await init();
    }
    Map<String, dynamic> values = {'update_time': DateTime.now().millisecondsSinceEpoch, 'is_synchronized': 0};
    if (images != null) {
      values['images'] = images;
    }
    if (text != null) {
      values['text'] = text;
    }
    return await _database.update('t_business_sync', values, where: 'id = ?', whereArgs: [id]) > 0;
  }

  batchUpdateBusinessSync() async {
    if (!_database.isOpen) {
      await init();
    }
    int now = DateTime.now().millisecondsSinceEpoch;
    return await _database.execute("update t_business_sync set is_synchronized=1, sync_time=$now");
  }

  deleteBusinessSync(String id) async {
    if (!_database.isOpen) {
      await init();
    }
    return await _database.delete('t_business_sync', where: 'id = ?', whereArgs: [id]) > 0;
  }

  Future<List<SyncBusinessEntity>> findBusinessSync({String? where, List? whereArgs}) async {
    if (!_database.isOpen) {
      await init();
    }
    List<Map<String, Object?>> list = await _database.query('t_business_sync', where: where, whereArgs: whereArgs);
    if (list.isEmpty) {
      return [];
    }
    return list.map((e) => SyncBusinessEntity.fromJson(e)).toList();
  }

  // --------------------------- common --------------------------------

  Future<List<Map<String, Object?>>> find({required String tableName, String? where, List? whereArgs, int? offset, int? limit}) async {
    if (!_database.isOpen) {
      await init();
    }

    if (where == null || where.isEmpty) {
      return await _database.query(tableName);
    }
    return await _database.query(tableName, where: where, whereArgs: whereArgs);
  }

  insert({required String tableName, required Map<String, Object> data}) async {
    if (!_database.isOpen) {
      await init();
    }
    return await _database.insert(tableName, data, conflictAlgorithm: ConflictAlgorithm.replace) > 0;
  }

  update({required String tableName, required int id, required Map<String, Object?> data}) async {
    if (!_database.isOpen) {
      await init();
    }
    return await _database.update(tableName, data, where: "id = ?", whereArgs: [id]);
  }

  delete({required String tableName, required int id}) async {
    if (!_database.isOpen) {
      await init();
    }
    return await _database.delete(tableName, where: 'id = ?', whereArgs: [id]) > 0;
  }

  clear({required tableName}) async {
    if (!_database.isOpen) {
      await init();
    }
    return _database.execute("delete from $tableName");
  }

  // -----------------------------------------------------------

  existTable({String tableName = 't_business_sync'}) async {
    if (!_database.isOpen) {
      await init();
    }
    List<Map<String, Object?>> rows = await _database.rawQuery("SELECT name FROM sqlite_master WHERE type='table' AND name='$tableName'");
    return rows.isNotEmpty && rows[0]['name'] == tableName;
  }

  close() async {
    return await _database.close();
  }

  createNewTable(buildTableSQL) async {
    return await _database.execute(buildTableSQL);
  }

  deleteTable({required tableName}) async {
    return await _database.execute("drop table $tableName");
  }

  batchInsert({required tableName, required List<dynamic> dataList, required String columns}) async {
    Batch batch = _database.batch();
    List<String> newCols = columns.split(",");
    for (final item in dataList) {
      String sql = "INSERT INTO $tableName ($columns) VALUES (";
      for (var field in newCols) {
        sql += "'${item[field.toCamelCase()] ?? ""}',";
      }
      sql = "${sql.substring(0, sql.length - 1)})";
      batch.rawInsert(sql);
    }
    return await batch.commit();
  }

  batchInsertProject(List<ProjectEntity> projects, String currentProjectId) async {
    Batch batch = _database.batch();
    for (final item in projects) {
      // 判断是否有child
      if (item.children.isNotEmpty) {
        batchInsertProject(item.children.map((e) => ProjectEntity.fromJson(e)).toList(), currentProjectId);
      }
      String sql = "insert into t_project (project_id,project_code,project_name, dept_code,dept_name, project_type,merge_name, parent_id, is_current) values "
          "('${item.projectId}', '${item.projectCode}', '${item.projectName}', '${item.deptCode}', '${item.deptName}','${item.projectType}','${item.mergeName}', '${item.parentId}', '${item.projectId == currentProjectId ? 1 : 0}')";
      batch.rawInsert(sql);
    }
    return await batch.commit();
  }
}
